﻿using BCCommon;
using BCEntity.TMS.MarketingCoupon.MarketingCouponAddress;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BCData.TMS.MarketingCoupon.MarketingCouponAddress
{
    public class MarketingCouponAddressData : IMarketingCouponAddressData
    {
        private readonly IDatabaseContext database;
        private readonly string QUERY = @"SELECT
		                                        A.MarketingCouponAddressId,
		                                        A.MarketingCouponTypeId,
		                                        A.CouponType,
		                                        A.CouponName,
		                                        A.CouponSummark,
		                                        A.StartDate,
		                                        A.EndDate,
		                                        A.CouponAmount,
		                                        A.CouponCount,
		                                        A.AgentRecordId,
		                                        A.IsCommon,
		                                        A.ProvinceCode,
		                                        A.CityCode,
		                                        A.CountyCode,
		                                        A.CreateAdminId,
		                                        A.CreateAdminName,
		                                        A.CreateTime
                                            FROM
                                                MarketingCouponAddress A";



        #region 构造函数
        public MarketingCouponAddressData(IDatabaseContext database)
        {
            this.database = database;
        }
        #endregion

        #region InsertAsync
        /// <summary>
        /// 添加新优惠券可用地址纪录
        /// </summary>
        /// <param name="marketingCouponAddressEntity"></param>
        /// <returns></returns>
        public async Task<MarketingCouponAddressEntity> InsertAsync(MarketingCouponAddressEntity entity)
        {
            return await this.database.Master.InsertAsync(entity, database.Transaction);
        }
        #endregion

        public async Task<int> InsertAllAsync(IEnumerable<MarketingCouponAddressEntity> entities)
        {
            return await this.database.Master.InsertAllAsync(entities, database.Transaction);
        }

        #region updateAsync
        /// <summary>
        /// 修改优惠券可用地址记录
        /// </summary>
        /// <param name="marketingCouponAddressEntity"></param>
        /// <returns></returns>
        public async Task<MarketingCouponAddressEntity> UpdateAsync(MarketingCouponAddressEntity entity)
        {
            return await this.database.Master.UpdateAsync(entity, database.Transaction);
        }
        #endregion 

        #region Delete
        /// <summary>
        /// 删除优惠券可用地址记录
        /// </summary>
        /// <param name="marketingCouponAddressId"></param>
        /// <returns></returns>
        public async Task<int> DeleteAsync(long marketingCouponAddressId)
        {
            string sql = "DELETE FROM marketingcouponaddress WHERE MarketingCouponAddressId = ?marketingCouponAddressId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?MarketingCouponAddressId", marketingCouponAddressId, DbType.Int64);
            return await this.database.Master.ExecuteAsync(sql, parameters, database.Transaction);
        }
        #endregion

        #region Delete
        /// <summary>
        /// 删除优惠券可用地址记录
        /// </summary>
        /// <param name="marketingCouponAddressId"></param>
        /// <returns></returns>
        public async Task<int> DeleteByTypeId(long marketingCouponTypeId)
        {
            string sql = "DELETE FROM marketingcouponaddress WHERE MarketingCouponTypeId = ?MarketingCouponTypeId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?MarketingCouponTypeId", marketingCouponTypeId, DbType.Int64);
            return await this.database.Master.ExecuteAsync(sql, parameters, database.Transaction);
        }
        #endregion

        #region GetById
        /// <summary>
        /// 获取优惠券可用地址记录
        /// </summary>
        /// <param name="marketingCouponAddressId"></param>
        /// <returns></returns>
        public async Task<MarketingCouponAddressEntity> GetEntityAsync(long marketingCouponAddressId)
        {
            string sql = QUERY + " WHERE  MarketingCouponAddressId = ?marketingCouponAddressId";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?MarketingCouponAddressId", marketingCouponAddressId, DbType.Int64);
            return await this.database.Slave.QueryEntityAsync<MarketingCouponAddressEntity>(sql, parameters);
        }
        #endregion

        #region GetListAsync 查询优惠券可用地址记录
        public async Task<(IEnumerable<MarketingCouponAddressEntity>, int)> GetListAsync(int pageIndex, int pageSize)
        {

            DynamicParameters parameters = new DynamicParameters();
            //查询条件
            List<string> wheres = new List<string>();


            string sql = string.Format(@"{0} {1} ; SELECT COUNT(1) FROM  marketingcouponaddress  {1}",
                   QUERY,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize
                  );

            return await this.database.Slave.QueryPageableListAsync<MarketingCouponAddressEntity>(sql, parameters);
        }

        public async Task<string> GetAddressStrByTypeId(long marketingCouponTypeId)
        {
            var sql = @" SELECT group_concat(B.CountyName)  FROM MarketingCouponAddress A
                            INNER JOIN county B ON A.CountyCode=B.CountyCode
                            WHERE MarketingCouponTypeId=?MarketingCouponTypeId";
            return await this.database.Slave.QueryFirstOrDefaultAsync<string>(sql, new { marketingCouponTypeId });
        }

        public async Task<IEnumerable<MarketingCouponAddressViewEntity>> GetListViewAsync(long marketingCouponTypeId)
        {
            var sqlQuery = @" SELECT
	                                A.MarketingCouponAddressId,
	                                A.MarketingCouponTypeId,
	                                A.CouponType,
	                                A.CouponName,
	                                A.CouponSummark,
	                                A.StartDate,
	                                A.EndDate,
	                                A.CouponAmount,
	                                A.CouponCount,
	                                A.AgentRecordId,
	                                A.IsCommon,
	                                A.ProvinceCode,
	                                B.ProvinceName,
	                                A.CityCode,
	                                B.CityName,
	                                A.CountyCode,
	                                B.CountyName,
	                                A.CreateAdminId,
	                                A.CreateAdminName,
	                                A.CreateTime
                                FROM MarketingCouponAddress A
                                INNER JOIN county B ON B.CountyCode=A.CountyCode ";
            string sql =  $" {sqlQuery} WHERE  A.MarketingCouponTypeId = ?MarketingCouponTypeId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?MarketingCouponTypeId", marketingCouponTypeId, DbType.Int64);
            return await this.database.Slave.QueryAsync<MarketingCouponAddressViewEntity>(sql, parameters);
        }
        #endregion

    }
}
